package controllers.entorno;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import controllers.servlets.DownloadServlet;
import com.sun.xml.internal.messaging.saaj.util.ByteOutputStream;

public class PaginaReporte extends PaginaBase {

	private static Connection getConexion() throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		return DriverManager.getConnection(
				"jdbc:oracle:thin:@192.168.14.128:1521:xe", "frodriguez",
				"root");
	}

	public void generarReporte(String query) throws Exception {

		HSSFWorkbook excel = new HSSFWorkbook();
		HSSFSheet sheet = excel.getSheetAt(0);

		Connection conn = getConexion();
		try {
			Statement st = conn.createStatement();
			try {
				ResultSet result = st.executeQuery(query);
				try {
					int numFila = 2;
					while (result.next()) {
						HSSFRow fila = sheet.getRow(numFila);
						int cantColumns = result.getMetaData().getColumnCount();
						for (int i = 1; i < cantColumns; i++) {
							fila.getCell(i).setCellValue(result.getString(i));
						}
						numFila++;
					}
				} finally {
					result.close();
				}

			} finally {
				st.close();
			}
		} finally {
			conn.close();
		}
		ByteOutputStream bos = new ByteOutputStream();
		try {
			excel.write(bos);
		} finally {
			bos.close();
		}

		byte[] bytes = bos.getBytes();
		getSessionScope().put(DownloadServlet.BYTES, bytes);
		((HttpServletResponse) getFacesContext().getExternalContext()
				.getResponse()).sendRedirect("/Download");
	}
}
